package DBTasks;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 
 * @author Stephen
 * 
 */

public class LargestMonthlyDonations extends DBTask {

    private static String makeViewSQL = "CREATE OR REPLACE VIEW MYV AS SELECT Month(Date) AS M, MAX(Amount) AS A, Shelter_Name AS N FROM Donation WHERE Year(Date) = ? AND Shelter_Name = ? Group BY M";

    private static String lmdSQL = "SELECT MONTH(Date), Name, Email, Phone, City, Amount FROM Donation, MYV, Donor " + "WHERE Donation.Amount = MYV.A " + "AND MONTH(Donation.Date) = MYV.M "
            + "AND Donation.Donor_Name = Donor.Name " + "AND Donation.Donor_Phone = Donor.Phone " + "AND YEAR(Donation.Date) = 2013 " + "AND Donation.Shelter_Name=MYV.N";

    @SuppressWarnings("deprecation")
	public static Map<Integer, List<Donation>> getLargestMonthlyDonations(String shelterName) {
        Map<Integer, List<Donation>> lmd = new HashMap<Integer, List<Donation>>();

        try {
            PreparedStatement makeView = con.prepareStatement(makeViewSQL);
            Date date = new Date();
            makeView.setInt(1, date.getYear() + 1900);
            makeView.setString(2, shelterName);
            makeView.execute();

            PreparedStatement lmdQuery = con.prepareStatement(lmdSQL);

            ResultSet rs = lmdQuery.executeQuery();

            while (rs.next()) {
                Donation donation = new Donation();

                int month = rs.getInt(1); // for the key of the hashmap

                donation.setName(rs.getString("Name"));
                donation.setEmail(rs.getString("Email"));
                donation.setPhoneNumber(rs.getString("Phone"));
                donation.setCity(rs.getString("City"));
                donation.setAmount(rs.getInt("Amount"));

                if (!lmd.containsKey(month)) {
                    lmd.put(month, new ArrayList<Donation>());
                }
                lmd.get(month).add(donation);

            }

            makeView.executeUpdate("DROP VIEW MYV");
        }
        catch (SQLException e) {
            e.printStackTrace();
            lmd = null;
        }
        return lmd;
    }

}
